import base64
import io
import os
from datetime import datetime
import matplotlib
import pandas as pd
import seaborn as sns
from flask import Flask, render_template, jsonify, send_from_directory, request
from pyexpat import features
from werkzeug.utils import secure_filename
from pyecharts import options as opts
from pyecharts.charts import HeatMap
# 这里假设 excelFileFormat 相关模块存在，若没有可根据实际情况修改
from excelFileFormat.config import UPLOAD_FOLDER
from excelFileFormat.utils.file_checker import allowed_file, exact_field_match
from excelFileFormat.utils.validators import is_valid_date_format, is_numeric
from pyecharts.charts import Bar, HeatMap
from pyecharts.charts import HeatMap, Pie
from pyecharts.charts import Line
from pyecharts.charts import Scatter


matplotlib.use('Agg')  # 设置为非交互式后端
import matplotlib.pyplot as plt
plt.rcParams['font.sans-serif'] = ['SimHei']  # 使用黑体
plt.rcParams['axes.unicode_minus'] = False  # 解决负号显示问题

app = Flask(__name__)
app.config["UPLOAD_FOLDER"] = UPLOAD_FOLDER
os.makedirs(UPLOAD_FOLDER, exist_ok=True)

ALLOWED_EXTENSIONS = {'xlsx'}

def allowed_file(filename):
    return '.' in filename and \
        filename.rsplit('.', 1)[1].lower() in ALLOWED_EXTENSIONS

@app.route("/upload", methods=["POST"])
def upload_excel():
    if "file" not in request.files:
        return jsonify({"error": "未检测到上传文件"}), 400

    file = request.files["file"]
    if file.filename == "":
        return jsonify({"error": "文件名为空"}), 400

    if not allowed_file(file.filename):
        return jsonify({"error": "不支持的文件格式，请上传Excel文件"}), 400

    filename = secure_filename(file.filename)
    filepath = os.path.join(app.config["UPLOAD_FOLDER"], filename)

    try:
        file.save(filepath)
        excel_data = pd.read_excel(filepath, sheet_name=None, dtype=str)

        expected_sheets = ["Load", "Weather"]
        for sheet_name in expected_sheets:
            if sheet_name not in excel_data:
                os.remove(filepath)
                return jsonify({"error": f"缺少工作表: {sheet_name}"}), 400

            df = excel_data[sheet_name]
            df.columns = df.columns.str.strip()

            # 字段校验
            is_match, message = exact_field_match(sheet_name, df)
            if not is_match:
                os.remove(filepath)
                return jsonify({"error": message}), 400

            # 日期格式校验
            if "YMD" in df.columns:
                invalid_dates = df[~df["YMD"].apply(is_valid_date_format)]
                if not invalid_dates.empty:
                    os.remove(filepath)
                    return jsonify({
                        "error": f"{sheet_name}表中存在非法日期格式，YMD 应为 'YYYYMMDD'"
                    }), 400

            # 数值类型校验
            numeric_cols = [col for col in df.columns if col != "YMD"]
            for col in numeric_cols:
                invalid_rows = df[~df[col].apply(is_numeric)]
                if not invalid_rows.empty:
                    os.remove(filepath)
                    return jsonify({
                        "error": f"{sheet_name}表字段 '{col}' 中存在非数值内容"
                    }), 400


        return jsonify({"success": True, "filename": filename}), 200


    except Exception as e:
        if os.path.exists(filepath):
            os.remove(filepath)
        return jsonify({"error": f"文件处理失败: {str(e)}"}), 500

@app.route('/predict/<filename>')
def predict_file(filename):
    return filename

@app.route('/preview/<filename>')
def preview_file(filename):
    filepath = os.path.join(app.config['UPLOAD_FOLDER'], secure_filename(filename))
    if not os.path.exists(filepath):
        return jsonify({"error": "文件未找到"}), 404

    try:
        # 读取 Excel 文件的所有 sheet 表
        excel_file = pd.ExcelFile(filepath)
        sheet_names = excel_file.sheet_names
        all_preview_data = {}

        for sheet_name in sheet_names:
            df = excel_file.parse(sheet_name)
            # 只预览前 20 行数据
            preview_data = df.head(20).fillna('').to_dict(orient='records')
            columns = list(df.columns)

            all_preview_data[sheet_name] = {
                'filename': filename,
                'columns': columns,
                'content': preview_data,
                'row_count': len(df),
                'size': os.path.getsize(filepath),
                'upload_time': datetime.fromtimestamp(os.path.getmtime(filepath)).strftime('%Y-%m-%d %H:%M')
            }

        return jsonify(all_preview_data)

    except Exception as e:
        return jsonify({"error": f"读取 Excel 文件失败: {str(e)}"}), 400

@app.route('/')
def data_analysis():
    return render_template('analysis.html', current_year=datetime.now().year)

@app.route('/history')
def data_history():
    return render_template('history.html',
                           charts={},
                           current_year=2025)

@app.route('/detail')
def data_detail():
    return render_template('detail.html',
                           charts={},
                           current_year=2023
                           )

@app.route('/yuce')
def data_yuce():
    return render_template('yuce.html',
                           charts={},
                           current_year=2023)

@app.route('/download-template')
def download_template():
    return send_from_directory(
        directory='static/images',
        path='moban.xlsx',
        as_attachment=True
    )
@app.route('/delete/<filename>', methods=['DELETE'])
def delete_file(filename):
    filepath = os.path.join(app.config['UPLOAD_FOLDER'], secure_filename(filename))
    if os.path.exists(filepath):
        try:
            os.remove(filepath)
            return jsonify({'success': True, 'message': '文件删除成功'})
        except Exception as e:
            return jsonify({'success': False, 'message': f'删除文件时出错: {str(e)}'}), 500
    else:
        return jsonify({'success': False, 'message': '文件未找到'}), 404

# 后面对接文件数据的接口
@app.route('/get_correlation_data', methods=['GET'])
def get_correlation_data():
    try:
        # 加载数据
        data_corr = pd.read_csv('../data/Area1_Load_clean.csv')
        weather_corr = pd.read_csv('../data/Area1_Weather_clean.csv')

        # 转换日期格式
        data_corr['YMD'] = pd.to_datetime(data_corr['YMD'])
        weather_corr['YMD'] = pd.to_datetime(weather_corr['YMD'])

        # 合并数据
        merged_corr = pd.merge(
            data_corr[['YMD', 'MeanLoad']],
            weather_corr[['YMD', 'Max_Temperature', 'Min_Temperature', 'Avg_Temperature', 'Avg_Humidity', 'Rainfall']],
            on='YMD',
            how='inner'
        )

        # 数据类型检查与转换
        numeric_columns = ['Max_Temperature', 'Min_Temperature', 'Avg_Temperature', 'Avg_Humidity', 'Rainfall',
                           'MeanLoad']
        for col in numeric_columns:
            merged_corr[col] = pd.to_numeric(merged_corr[col], errors='coerce')

        # 移除包含 NaN 的行
        merged_corr = merged_corr.dropna(subset=numeric_columns)

        # 筛选2012 - 2014年数据
        time_mask = (merged_corr['YMD'] >= '2012-01-01') & (merged_corr['YMD'] < '2015-01-01')
        merged_corr = merged_corr[time_mask]

        # 日志输出（可在控制台查看）
        app.logger.info(f"数据统计信息:\n{merged_corr[numeric_columns].describe()}")
        app.logger.info(f"最高温度: {merged_corr['Max_Temperature'].max()}")

        # 计算相关系数
        correlation = merged_corr[numeric_columns].corr()

        # 保留两位小数
        correlation = correlation.round(2)
        stats = merged_corr[numeric_columns].describe().round(2)

        # 使用 pyecharts 绘制热力图
        rows, columns = correlation.shape
        data = []
        for i in range(rows):
            for j in range(columns):
                data.append([i, j, correlation.iloc[i, j]])

        heatmap = (
            HeatMap()
            .add_xaxis(correlation.columns.tolist())
            .add_yaxis("相关性", correlation.index.tolist(), data)
            .set_global_opts(
                title_opts=opts.TitleOpts(title="相关性热力图"),
                visualmap_opts=opts.VisualMapOpts(min_=-1, max_=1,pos_right="2%",pos_bottom='11%'),
                toolbox_opts=opts.ToolboxOpts(is_show=True,pos_left='18%'),
            )
        )

        # 生成 HTML 代码
        heatmap_html = heatmap.render_embed()

        # 转换为字典并返回
        return jsonify({
            'status': 'success',
            'data': correlation.to_dict(),
            'stats': stats.to_dict(),  # 只返回数值列的统计信息
            'heatmap': heatmap_html
        })

    except Exception as e:
        app.logger.error(f"相关系数API错误: {str(e)}")
        return jsonify({'status': 'error', 'message': str(e)}), 500

#节假日图接口
@app.route('/get_load_comparison', methods=['GET'])
def get_load_comparison():
    try:
        # 加载数据
        data = pd.read_csv('../data/Area1_Load_clean.csv')
        data['YMD'] = pd.to_datetime(data['YMD'])

        # 确保 Holiday_Name 列存在
        if 'Holiday_Name' not in data.columns:
            data['Holiday_Name'] = None

        # 选取样本日期
        workday = data[data['YMD'] == '2011-04-11']  # 周一（工作日）
        holiday = data[(data['Holiday_Name'] == 'Spring Festival') & (data['YMD'] == '2011-02-02')]

        # 如果没有找到节假日数据，使用其他日期替代
        if holiday.empty:
            holiday = data[data['YMD'] == '2011-04-17']  # 周日（可替换为其他合适日期）

        # 计算一天的平均负荷
        workday_avg_load = workday['MeanLoad'].mean()
        holiday_avg_load = holiday['MeanLoad'].mean()

        # 使用 pyecharts 绘制条形图
        bar = (
            Bar()
            .add_xaxis(["工作日", "节假日"])
            .add_yaxis("平均负荷", [workday_avg_load, holiday_avg_load])
            .set_global_opts(
                title_opts=opts.TitleOpts(title="节假日负荷对比"),
                toolbox_opts=opts.ToolboxOpts(is_show=True,pos_left='18%')
            )
        )

        # 生成 HTML 代码
        bar_html = bar.render_embed()

        # 转换为字典并返回
        return jsonify({
            'status': 'success',
            'workday_avg_load': workday_avg_load,
            'holiday_avg_load': holiday_avg_load,
            'chart_html': bar_html
        })

    except Exception as e:
        app.logger.error(f"节假日负荷对比API错误: {str(e)}")
        return jsonify({'status': 'error', 'message': str(e)}), 500

# 新  增路由用于生成气象因素对负荷影响的饼状图
@app.route('/get_weather_load_pie_chart', methods=['GET'])
def get_weather_load_pie_chart():
    try:
        # 读取数据
        data = pd.read_csv('../data/Area1_Load_Weather_Time.csv')

        # 假设我们计算各气象因素与负荷的简单比例
        weather_factors = ['Max_Temperature', 'Min_Temperature', 'Avg_Temperature', 'Avg_Humidity', 'Rainfall']
        load = data['Load'].values[0]
        factor_ratios = []
        for factor in weather_factors:
            factor_value = data[factor].values[0]
            ratio = factor_value / load if load != 0 else 0
            factor_ratios.append([factor, ratio])

        # 创建饼状图
        pie = (
            Pie()
            .add(
                "",
                factor_ratios,
                radius=["30%", "75%"],
            )
            .set_global_opts(
                title_opts=opts.TitleOpts(title="气象与负荷相关性分析"),
                legend_opts=opts.LegendOpts(orient="vertical", pos_top="15%", pos_left="2%"),
                toolbox_opts=opts.ToolboxOpts(is_show=True,pos_left="22%")
            )
            .set_series_opts(
                label_opts=opts.LabelOpts(formatter="{b}: {d}%")
            )
        )

        # 生成 HTML 代码
        pie_html = pie.render_embed()

        # 返回数据
        return jsonify({
            'status': 'success',
            'chart_html': pie_html
        })

    except Exception as e:
        app.logger.error(f"气象因素对负荷影响饼状图API错误: {str(e)}")
        return jsonify({'status': 'error', 'message': str(e)}), 500

#feature字段转中文
feature_mapping = {
    'Max_Temperature': '最高温度',
    'Min_Temperature': '最低温度',
    'Avg_Temperature': '平均温度',
    'Avg_Humidity': '平均湿度',
    'Rainfall': '降雨量'
}
#用电量与影响因素的关系接口
@app.route('/get_relationship_data', methods=['GET'])
def get_relationship_data():
    try:
        feature = request.args.get('feature')
        if not feature:
            return jsonify({'status': 'error', 'message': '未提供特征参数'}), 400

        # 加载数据
        data_corr = pd.read_csv('../data/Area1_Load_clean.csv')
        weather_corr = pd.read_csv('../data/Area1_Weather_clean.csv')

        # 转换日期格式
        data_corr['YMD'] = pd.to_datetime(data_corr['YMD'])
        weather_corr['YMD'] = pd.to_datetime(weather_corr['YMD'])

        # 合并数据
        merged_corr = pd.merge(
            data_corr[['YMD', 'MeanLoad']],
            weather_corr[['YMD', feature]],
            on='YMD',
            how='inner'
        )

        # 数据类型检查与转换
        numeric_columns = [feature, 'MeanLoad']
        for col in numeric_columns:
            merged_corr[col] = pd.to_numeric(merged_corr[col], errors='coerce')

        # 移除包含 NaN 的行
        merged_corr = merged_corr.dropna(subset=numeric_columns)

        # 筛选2012 - 2014年数据
        time_mask = (merged_corr['YMD'] >= '2012-01-01') & (merged_corr['YMD'] < '2015-01-01')
        merged_corr = merged_corr[time_mask]

        # 使用 pyecharts 绘制折线图
        line = (
            Line()
            .add_xaxis(merged_corr['YMD'].dt.strftime('%Y-%m-%d').tolist())
            .add_yaxis("用电量", merged_corr['MeanLoad'].tolist())
            .add_yaxis(feature, merged_corr[feature].tolist())
            .set_global_opts(
                # 使用映射字典获取中文名称
                title_opts=opts.TitleOpts(title=f"用电量与{feature_mapping.get(feature, feature)}的关系"),
                toolbox_opts=opts.ToolboxOpts(is_show=True,pos_left='68%'),
                xaxis_opts=opts.AxisOpts(name="日期"),
                yaxis_opts=opts.AxisOpts(name="数值"),
            )
        )

        # 生成 HTML 代码
        chart_html = line.render_embed()

        # 转换为字典并返回
        return jsonify({
            'status': 'success',
            'chart_html': chart_html
        })

    except Exception as e:
        app.logger.error(f"用电量与特征关系API错误: {str(e)}")
        return jsonify({'status': 'error', 'message': str(e)}), 500


# 新增：获取负荷曲线数据接口
@app.route('/get_load_curve_data', methods=['GET'])
def get_load_curve_data():
    try:
        # 加载数据
        data = pd.read_csv('../data/Area1_Load_clean.csv')

        # 确保日期列存在并转换为datetime类型
        if 'YMD' not in data.columns:
            return jsonify({'status': 'error', 'message': '数据中缺少日期列(YMD)'}), 400

        data['YMD'] = pd.to_datetime(data['YMD'])

        # 按日期分组计算每日的最大、平均和最小负荷
        daily_load = data.groupby('YMD').agg({
            'MaxLoad': 'max',
            'MeanLoad': 'mean',
            'MinLoad': 'min'
        }).reset_index()

        # 准备图表数据
        x_data = daily_load['YMD'].dt.strftime('%Y-%m-%d').tolist()
        max_load = daily_load['MaxLoad'].tolist()
        mean_load = daily_load['MeanLoad'].tolist()
        min_load = daily_load['MinLoad'].tolist()

        # 使用 pyecharts 绘制散点图
        scatter = (
            Scatter()
            .add_xaxis(x_data)
            .add_yaxis("日最大负荷", max_load, symbol_size=6, color="#ff4d4f",label_opts=opts.LabelOpts(is_show=False))
            .add_yaxis("日平均负荷", mean_load, symbol_size=6, color="#1890ff",label_opts=opts.LabelOpts(is_show=False))
            .add_yaxis("日最小负荷", min_load, symbol_size=6, color="#52c41a",label_opts=opts.LabelOpts(is_show=False))
            .set_global_opts(
                title_opts=opts.TitleOpts(title="时刻负荷量趋势图"),
                toolbox_opts=opts.ToolboxOpts(is_show=True,pos_left="68%"),
                xaxis_opts=opts.AxisOpts(name="日期"),
                yaxis_opts=opts.AxisOpts(name="负荷量"),
                legend_opts=opts.LegendOpts(pos_top="5%"),
                datazoom_opts=[opts.DataZoomOpts()],  # 添加数据缩放功能
            )
        )

        # 生成 HTML 代码
        chart_html = scatter.render_embed()

        return jsonify({
            'status': 'success',
            'chart_html': chart_html
        })

    except Exception as e:
        app.logger.error(f"负荷曲线API错误: {str(e)}")
        return jsonify({'status': 'error', 'message': str(e)}), 500

if __name__ == '__main__':
    app.run(debug=True)